﻿using System;
using System.Collections;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Web.UI.WebControls;

namespace proekt4
{
    /// <summary>
    /// Summary description for univerziteti
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class univerziteti : System.Web.Services.WebService
    {

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
        public univerziteti()
             {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
            }
        [WebMethod]

        public int VratiCena(int id)
        {


            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            using (conn)
            {
                conn.Open();

                string sql = "SELECT cena FROM univerziteti WHERE ID=@id";
                OleDbCommand komanda = new OleDbCommand(sql, conn);
                komanda.Parameters.AddWithValue("@id", id);

                OleDbDataReader citac = komanda.ExecuteReader();
                citac.Read();
                return Convert.ToInt32(citac["cena"].ToString());

            }
            return -1;
        }

        [WebMethod]

        public string VratiIme(string id)
        {

            string greska = "";
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            using (conn)
            {
                conn.Open();

                string sql = "SELECT ime FROM univerziteti WHERE ID=@id_c";
                OleDbCommand komanda = new OleDbCommand(sql, conn);
                komanda.Parameters.AddWithValue("@id", Convert.ToInt32(id));

                OleDbDataReader citac = komanda.ExecuteReader();
                citac.Read();
                return citac["ime"].ToString();

            }
            greska = "nema takov univerzitet";
            return greska;
        }
        [WebMethod]
        public DataSet VratiUniverzitet()
        {
            DataSet ds = Ispolni();
            return ds;

        }
        protected DataSet Ispolni()
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            string sql = "SELECT  ID, ime, cena, grad FROM univerziteti";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            conn.Open();
            adapter.Fill(ds, "univerziteti");
            conn.Close();
            return ds;
        }

        public class Univerzitet
        {
            public string id;
            public string grad;
            public string ime;
            public string cena;
        }
        [WebMethod]
        public bool PromeniUniverzitet(Univerzitet u)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            string sql = "UPDATE univerziteti SET ime=@ime, cena=@cena, grad=@grad WHERE ID=@id";
            OleDbCommand komanda = new OleDbCommand(sql, conn);
            komanda.Parameters.AddWithValue("@ime", u.ime);
            komanda.Parameters.AddWithValue("@cena", u.cena);
            komanda.Parameters.AddWithValue("@grad", u.grad);
            komanda.Parameters.AddWithValue("@id", u.id);
            int status = 0;
            try
            {
                conn.Open();
                status = komanda.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                conn.Close();

            }
            return (status == 1);
        }

        [WebMethod]
        public bool DodajUniverzitet(Univerzitet u)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            string sql = "INSERT INTO univerziteti ( ID, ime, cena, grad ) VALUES ( @id, @ime, @cena, @grad ) ";
            OleDbCommand komanda = new OleDbCommand(sql, conn);
            komanda.Parameters.AddWithValue("@id", u.id);
            komanda.Parameters.AddWithValue("@ime", u.ime);
            komanda.Parameters.AddWithValue("@cena", u.cena);
            komanda.Parameters.AddWithValue("@grad", u.grad);
            int status = 0;
            try
            {
                conn.Open();
                status = komanda.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                conn.Close();

            }
            return (status == 1);
        }

        [WebMethod]
        public bool BrisiUniverzitet(Univerzitet u)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            string sql = "DELETE FROM univerziteti WHERE ID= @id ";
            OleDbCommand komanda = new OleDbCommand(sql, conn);
            komanda.Parameters.AddWithValue("@id", u.id);
            int status = 0;
            try
            {
                conn.Open();
                status = komanda.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                conn.Close();

            }
            return (status == 1);
        }
    }
    
}
